package com.awspaas.user.apps.cpq.controller;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.actionsoft.bpms.bo.engine.BO;
import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.commons.mvc.view.ResponseObject;
import com.actionsoft.bpms.server.UserContext;
import com.actionsoft.bpms.util.DBSql;
import com.actionsoft.sdk.local.SDK;
import com.alibaba.fastjson.JSONObject;

public class CPQ_GetAllRecordModuleNumberWeb {

	public String getAllModuleNumber(UserContext userContext, String bindId, String cs, String datas) {
		Map<String, JSONObject> pMap = new HashMap<String, JSONObject>();
		Map<String, String> nameMap = new HashMap<String, String>();
		Map<String, String> noMap = new HashMap<>();
		List<BO> ps=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS").bindId(bindId).list();
		List<BO> dataBO=SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").bindId(bindId).list();
		if(ps.size()>0) {
			for (int i = 0; i < ps.size(); i++) {
				//JSONObject json = (JSONObject) list.get(i);
				BO json =  ps.get(i);
				String bh = json.getString("PARMENTIER_NO");
				String value =  json.getString("PARMENTIER_VALUE");
				String name =  json.getString("PARMENTIER_NAME");
				String dw =  json.getString("DW");
				JSONObject jsonObject=new JSONObject();
				//判断value是否为数据
				if( NumberUtils.isCreatable(value)) {
					jsonObject.put("type", "1");//值为1者表示value为数值
				}else {
					jsonObject.put("type", "0");//值为0者表示value为字符串
				}
				jsonObject.put("value", value);
				pMap.put(bh, jsonObject);
				nameMap.put(bh, name);
				int num = i + 1;
				//noMap.put(bh, "B$" + num);
			}
			System.out.println("pMap:"+pMap.toString());
		}
		// 获取模块编码信息
				Map<String, BO> idMap = new HashMap<>();
				String bms = "";
				for (BO json : dataBO) {
					//JSONObject json = (JSONObject) string;
					String bm = json.getString("MKBH");
					if (bms.isEmpty()) {
						bms = "'" + bm + "'";
					} else {
						bms += ",'" + bm + "'";
					}
					String id = json.getString("ID");
					idMap.put(id, json);
				}
				/**
				 * 获取物料中制造件和装配件的原材料信息，用于原材料重量的计算
				 */
				String  clsql="select a.BM,a.MC,a.LX,a.XH,a.ZL,b.BM as BH from   BO_EU_CPQ_COMPONENT_PART_CLYL a,BO_EU_CPQ_COMPONENT_PART b"
						+ " where a.BINDID=b.BINDID and  b.TYPE in('装配件','制造件')";
				List<RowMap> clmaps=DBSql.getMaps(clsql);
				//1、将所涉及的装配件和制造件的材料重量信息存放在map中。
				Map<String, List<RowMap>> aboutCPMap = new HashMap<>();
				if (clmaps.size() > 0) {
					for (RowMap rowMap : clmaps) {
						String bh = rowMap.getString("BH");
						if (aboutCPMap.containsKey(bh)) {
							List<RowMap> lists = aboutCPMap.get(bh);
							lists.add(rowMap);
							aboutCPMap.put(bh, lists);
						} else {
							List<RowMap> lists = new ArrayList<RowMap>();
							lists.add(rowMap);
							aboutCPMap.put(bh, lists);
						}

					}
				}
				//用于存放计算后的材料重量
				Map<String, JSONObject> zclMaps = new HashMap<>();

				String sql = "select  * from  BO_EU_CPQ_MODULE_RECORD where  GLPID=?  and MKBM in (" + bms + ")";
				String wlsql = "select  b.MKBM,a.* from BO_EU_CPQ_MODULE_RECORD_GRID a left join BO_EU_CPQ_MODULE_RECORD b "
						+ "on a.BINDID=b.BINDID  where  b.GLPID=?  and b.MKBM in (" + bms + ")";
				String cssql = "select  b.MKBM,a.* from BO_EU_CPQ_MODULE_RECORD_PARAMETERS a left join BO_EU_CPQ_MODULE_RECORD b "
						+ "on a.BINDID=b.BINDID  where  b.GLPID=?  and b.MKBM in (" + bms + ")";
				String zfsql = "select  b.MKBM,a.* from BO_EU_CPQ_MODULE_RECORD_STRAIGHT_HAIR a left join BO_EU_CPQ_MODULE_RECORD b "
						+ "on a.BINDID=b.BINDID  where  b.GLPID=?  and b.MKBM in (" + bms + ")";
				List<RowMap> maps = DBSql.getMaps(sql,new Object[] {bindId});
				Map<String, RowMap> moduleMap = new HashMap<>();
				if (maps.size() > 0) {
					for (RowMap rowMap : maps) {
						String mkbm = rowMap.getString("MKBM");
						moduleMap.put(mkbm, rowMap);
					}
				}
				List<RowMap> wlmaps = DBSql.getMaps(wlsql,new Object[] {bindId});
				Map<String, List<RowMap>> wlmoduleMap = new HashMap<>();
				if (wlmaps.size() > 0) {
					for (RowMap rowMap : wlmaps) {
						String mkbm = rowMap.getString("MKBM");
						if(wlmoduleMap.containsKey(mkbm)) {
							List<RowMap> list=wlmoduleMap.get(mkbm);
							list.add(rowMap);
							wlmoduleMap.put(mkbm, list);
						}else {
							List<RowMap> list=new ArrayList<RowMap>();
							list.add(rowMap);
							wlmoduleMap.put(mkbm, list);
						}
					}
				}
				List<RowMap> csmaps = DBSql.getMaps(cssql,new Object[] {bindId});
				Map<String, List<RowMap>> csmoduleMap = new HashMap<>();
				if (csmaps.size() > 0) {
					for (RowMap rowMap : csmaps) {
						String mkbm = rowMap.getString("MKBM");
						if(csmoduleMap.containsKey(mkbm)) {
							List<RowMap> list=csmoduleMap.get(mkbm);
							list.add(rowMap);
							csmoduleMap.put(mkbm, list);
						}else {
							List<RowMap> list=new ArrayList<RowMap>();
							list.add(rowMap);
							csmoduleMap.put(mkbm, list);
						}
					}
				}
				List<RowMap> zfmaps = DBSql.getMaps(zfsql,new Object[] {bindId});
				Map<String, List<RowMap>> zfmoduleMap = new HashMap<>();
				if (zfmaps.size() > 0) {
					for (RowMap rowMap : zfmaps) {
						String mkbm = rowMap.getString("MKBM");
						if(zfmoduleMap.containsKey(mkbm)) {
							List<RowMap> list=zfmoduleMap.get(mkbm);
							list.add(rowMap);
						}else {
							List<RowMap> list=new ArrayList<RowMap>();
							list.add(rowMap);
							zfmoduleMap.put(mkbm, list);
						}
					}
				}
				// 对模块数量和模块单价进行计算
				List<BO> bos = SDK.getBOAPI().query("BO_EU_CPQ_PROJECT_MSG_GRIDS").bindId(bindId).list();
				//存放已经保存的数据
				Map<String, BO> boMap = new HashMap<>();
				for (BO bo : bos) {
					boMap.put(bo.getString("ID"), bo);
				}
				List<String> sqlList=new ArrayList<>();
				List<String> sqlLists=new ArrayList<>();
				List<String> mkList=new ArrayList<>();
				List<String> csLists=new ArrayList<>();
				List<String> wlList=new ArrayList<>();
				List<String> zfLists=new ArrayList<>();
				for (String  id : idMap.keySet()) {
					//存放当前模块中所涉及的材料重量信息
					Map<String, JSONObject> clMaps = new HashMap<>();
					Workbook workbookwl = new XSSFWorkbook();
					Workbook workbook = new XSSFWorkbook();
					try {
						if (idMap.containsKey(id)) {
							BO json = idMap.get(id);
							String bm = json.getString("MKBH");
							System.out.println("模块名称："+json.getString("XDXH")+"，编码："+bm);
							// 获取模块维护台账的模块数据
							RowMap rowMap = moduleMap.get(bm);
							if (rowMap == null) {
								System.out.println("模块名称："+json.getString("XDXH")+"，编码："+bm+"未维护");
								continue;
							}
							String type = rowMap.getString("SITRUE");// 是否动态计算单价模块
							double MKDJ = 0;// 期货价
							//double TJ = Double.parseDouble(rowMap.getString("TJ").isEmpty()?"0":rowMap.getString("TJ"));// 体积
							//double ZL = Double.parseDouble(rowMap.getString("ZL").isEmpty()?"0":rowMap.getString("ZL"));// 重量
							double CLF = 0;// 期货价材料费
							double RGF = 0;// 人工费
							double WXWG = 0;// 外协费
							double CGF = 0;// 采购费
							/**
							 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
							 */
							/**
							 * 计算物料数量和成本。
							 */

							/************************** 通过excel公式进行计算 ***********************************/
							List<RowMap> materMap = new ArrayList<>();
							Sheet sheet = workbookwl.createSheet("Sheet1");
							for (int i = 0; i < ps.size(); i++) {
								int num = i + 1;
								Cell cellB1 = sheet.createRow(i).createCell(1);
								//JSONObject jsonObject = (JSONObject) list.get(i);
								BO jsonObject =  ps.get(i);
								String bh =  jsonObject.getString("PARMENTIER_NO");
								JSONObject jsonObject2=	pMap.get(bh);
								String lx=jsonObject2.getString("type");
								if(lx.equals("0")) {
									cellB1.setCellValue(jsonObject2.getString("value"));
									System.out.println("cellB1:"+cellB1.getStringCellValue());
								}else {
									String value=jsonObject2.getString("value");
									double  numValue=Double.parseDouble(value);
									 cellB1.setCellValue(numValue);
									 System.out.println("cellB1:"+cellB1.getNumericCellValue());
								}
								noMap.put(bh, "B$" + num);
							}
							List<RowMap> materials=new ArrayList<>();
							if(wlmoduleMap.containsKey(bm)) {
								materials=wlmoduleMap.get(bm);
								if(materials.size()>0) {
									for (int i = 0; i < materials.size(); i++) {
										RowMap bo = materials.get(i);
										String equation = bo.getString("WLJSGS");
										  Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										  if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											  System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
												for (String key : pMap.keySet()) {
													int num = newQuation.indexOf(key);
													if (num > -1) {
														newQuation = newQuation.replace(key, noMap.get(key));
													}
												}
												System.out.println("转换后的参数计算公式："+newQuation);
												// 计算出物料数量和当前模块的物料成本价格
												FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
												Cell cellB1 = sheet.createRow(ps.size()+i).createCell(2);
												cellB1.setCellFormula(newQuation);
												formulaEvaluator.evaluateFormulaCell(cellB1);
												// 获取公式计算结果
												CellValue cellValue = formulaEvaluator.evaluate(cellB1);
												System.out.println("The result of the formula is: " + cellValue.getNumberValue());
												String price1 = bo.getString("DJ").isEmpty()?"0":bo.getString("DJ");
												//String zl = bo.getString("ZL").isEmpty()?"0":bo.getString("ZL");
												//String tj = bo.getString("TJ").isEmpty()?"0":bo.getString("TJ");
												String clf = bo.getString("CLF").isEmpty()?"0":bo.getString("CLF");
												String rgf = bo.getString("RGF").isEmpty()?"0":bo.getString("RGF");
												String wx = bo.getString("WXWG").isEmpty()?"0":bo.getString("WXWG");
												String cgf = bo.getString("CGF").isEmpty()?"0":bo.getString("CGF");
												double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
												//double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
												//double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
												double clftotal1 = Double.parseDouble(clf) * cellValue.getNumberValue();
												double rgftotal = Double.parseDouble(rgf) * cellValue.getNumberValue();
												double wxtotal = Double.parseDouble(wx) * cellValue.getNumberValue();
												double cgftotal = Double.parseDouble(cgf) * cellValue.getNumberValue();
												System.out.println("物料名称:"+bo.getString("WLMC")+"单价："+price1+",总价："+total1);
												// 将数量和成本价格放入物料map集合中
												bo.put("SL", cellValue.getNumberValue());
												bo.put("ZJ", total1);
												//bo.set("ZZL", zltotal);
												//bo.set("ZTJ", tjtotal);
												bo.put("ZCGF", cgftotal);
												bo.put("ZGSF", rgftotal);
												bo.put("ZWX", wxtotal);
												bo.put("ZCLF",clftotal1);
												 String upSql= "update BO_EU_CPQ_MODULE_RECORD_GRID set SL= '"+cellValue.getNumberValue()+"',ZJ= '"+total1+"' ,ZCGF='"+cgftotal+"',ZGSF='"+rgftotal+"',ZWX='"+wxtotal+"',ZCLF='"+clftotal1+"' where ID='"+rowMap.getString("ID")+"' ; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
													wlList.add(upSql);


										  }
										  materMap.add(bo);
									}
									// 计算出模块的单价，每个物料总价相加
									double mkPrice1 = 0;
									//double zzlPrice = 0;
									//double ztjPrice = 0;
									double zcgfPrice = 0;
									double zgsfPrice = 0;
									double zwxPrice = 0;
									double zclfPrice1 = 0;
									for (RowMap bo : materMap) {
										String zjString1 = bo.getString("ZJ");
										//String zzlString = bo.getString("ZZL");
										//String ztjString = bo.getString("ZTJ");
										String zcgfString = bo.getString("ZCGF");
										String zgsfString = bo.getString("ZGSF");
										String zwxString = bo.getString("ZWX");
										String zclfString1 = bo.getString("ZCLF");
										mkPrice1 = mkPrice1 + Double.parseDouble(zjString1);
										//zzlPrice = zzlPrice + Double.parseDouble(zzlString);
										//ztjPrice = ztjPrice + Double.parseDouble(ztjString);
										zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
										zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
										zwxPrice = zwxPrice + Double.parseDouble(zwxString);
										zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString1);
										/**
										 * 对原材料重量进行计算
										 *
										 */
										if(aboutCPMap.containsKey(bo.getString("WLBM"))) {
											List<RowMap> lists=aboutCPMap.get(bo.getString("WLBM"));
											if(lists.size()>0) {
												String SL=bo.getString("SL");//数量
												BigDecimal sl=new BigDecimal(SL);
												for (RowMap cls : lists) {
													System.out.println("物料编码："+bo.getString("WLBM")+"材料信息:"+cls.toString());
													String clbm=cls.getString("BM");//编码
													String clmc=cls.getString("MC");//名称
													String cllx=cls.getString("LX");//类型
													String clxh=cls.getString("XH");//型号
													String cldw=cls.getString("DW");//单位
													String clzl=cls.getString("ZL").isEmpty()?"0":cls.getString("ZL");//重量
													BigDecimal zl=new BigDecimal(clzl);
													BigDecimal addzl= sl.multiply(zl);
													if(clMaps.containsKey(clbm)) {
														JSONObject jsonObject=clMaps.get(clbm);
														String zlold=jsonObject.getString("ZL");
														BigDecimal zlOld= new BigDecimal(zlold);
														zlOld=zlOld.add(addzl);
														jsonObject.put("ZL", zlOld);
														clMaps.put(clbm, jsonObject);
													}else {
														JSONObject jsonObject=new JSONObject();
														jsonObject.put("BM",clbm );
														jsonObject.put("MC",clmc );
														jsonObject.put("LX",cllx);
														jsonObject.put("XH",clxh);
														jsonObject.put("DW",cldw );
														jsonObject.put("ZL",addzl );
														clMaps.put(clbm, jsonObject);
													}
												}
											}
										}
									}
									MKDJ = mkPrice1;

									//TJ = ztjPrice;
									//ZL = zzlPrice;
									CLF = zclfPrice1;
									RGF = zgsfPrice;
									WXWG = zwxPrice;
									CGF = zcgfPrice;

								}
							}
							//计算直发物资运费
							double ZFYF = 0;// 直发物资运费价格
							List<RowMap> zfList=new  ArrayList<>();
							if(zfmoduleMap.containsKey(bm)) {
								 zfList=zfmoduleMap.get(bm);
								if(zfList.size()>0) {
									 for (int i = 0; i < zfList.size(); i++) {
										   RowMap bo2=zfList.get(i);
										   String sljsgs=bo2.getString("JSGS");//直发运费数量计算公式；
										   String yfdj=bo2.getString("YFBZ").isEmpty()?"0":bo2.getString("YFBZ");//运费单价；
										   double    price=Double.parseDouble(yfdj) ;
										   String SL=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费单价；
					                		  //double sl =Double.parseDouble(SL);
					                		 // double zj =price*sl;
											 //   ZFYF=ZFYF+zj;
											    Document document = Jsoup.parse(sljsgs);
												String newQuation=document.text();
												 if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
													  for (String key : pMap.keySet()) {
															int num = newQuation.indexOf(key);
															if (num > -1) {
																newQuation = newQuation.replace(key, noMap.get(key));
															}
														}
													  FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
														Cell cellB1 = sheet.createRow(ps.size()+materials.size()+1+i).createCell(2);
														cellB1.setCellFormula(newQuation);
														formulaEvaluator.evaluateFormulaCell(cellB1);
														// 获取公式计算结果
														CellValue cellValue = formulaEvaluator.evaluate(cellB1);
														System.out.println("The result of the formula is: " + cellValue.getNumberValue());
														ZFYF=ZFYF+cellValue.getNumberValue()*price;
														 String upSql= "update BO_EU_CPQ_MODULE_RECORD_STRAIGHT_HAIR set SL= '"+cellValue.getNumberValue()+"',YFJG= '"+cellValue.getNumberValue()*price+"'  where ID='"+bo2.getString("ID")+"' ; ";
															//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
															sqlLists.add(upSql);

											}else {
												String sl=bo2.getString("SL").isEmpty()?"0":bo2.getString("SL");//运费数量;
												 double num=Double.parseDouble(sl);
												ZFYF=ZFYF+num*price;
											}


										}
									 }

							}
							MKDJ=MKDJ+ZFYF;
							//将计算后的物料信息更新到模块记录清单表中,计算制造费
							BigDecimal clf = new BigDecimal(CLF);
							BigDecimal rgf = new BigDecimal(RGF);
							BigDecimal djnum = new BigDecimal(MKDJ);
							 String bl=rowMap.getString("ZZFBL").isEmpty()?"0":rowMap.getString("ZZFBL");
								BigDecimal blnum = new BigDecimal(bl);
								BigDecimal num2 = new BigDecimal("100");
								BigDecimal num1 = new BigDecimal("1");
								BigDecimal zzf=	clf.add(rgf).divide(num1.subtract(blnum.divide(num2)), 4).multiply(blnum.divide(num2));
							String mkupSql= "update BO_EU_CPQ_MODULE_RECORD set MKDJ= '"+MKDJ+"',CLF= '"+CLF+"',RGF='"+RGF+"',WXWG='"+WXWG+"',"
									+ "CGF='"+CGF+"',ZFWZYF='"+ZFYF+"',ZZF='"+zzf+"',DJZZF='"+zzf.add(djnum)+"'  where ID='"+rowMap.getString("ID")+"' ; ";
							//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
							mkList.add(mkupSql);
							String rule = rowMap.getString("SLJSGZ"); // 计算规则
							/**
							 * 计算模块数量。
							 */
							double number1 = 0;
							if (!rule.equals("0") && !rule.isEmpty()) {
								Document document = Jsoup.parse(rule);
								  String newRule=document.text();
								//Map<String, BO> materMap = new HashMap<>();
								System.out.println("模块计算规则新："+newRule);
								FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
								// 将物料中的计算规则的参数替换为指定的excel参数
								// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
								for (String key : pMap.keySet()) {
									int num = newRule.indexOf(key);
									if (num > -1) {
										newRule = newRule.replace(key, noMap.get(key));
									}
								}
								// 计算出物料数量和当前模块的物料成本价格
								Cell cellB1 = sheet.createRow(ps.size()+materials.size()+zfList.size()+3).createCell(3);
								cellB1.setCellFormula(newRule);
								System.out.println("cellB1value:"+cellB1.getCellFormula());
								formulaEvaluator.evaluateFormulaCell(cellB1);
								// 获取公式计算结果
								CellValue cellValue = formulaEvaluator.evaluate(cellB1);
								System.out.println("The result of the formula is: " + cellValue.getNumberValue());
								double number = cellValue.getNumberValue();
								number1=number;
								double MKDJ_price1 = MKDJ;
								double MKTotalPrice1 = number * MKDJ_price1;
								//double zlTotalPrice = number * ZL;
								//double tjTotalPrice = number * TJ;
								double clfTotalPrice1 = number * CLF;
								double rgfTotalPrice = number * RGF;
								double wxTotalPrice = number * WXWG;
								double cgfTotalPrice = number * CGF;
								String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',SL='"+number+"',DW='"+rowMap.getString("DW")+"',GG='"+rowMap.getString("GG")+"',ZJ='"+MKTotalPrice1+"'"
								 		+ ",ZCLF='"+clfTotalPrice1+"',ZGSF='"+rgfTotalPrice+"',ZWX='"+wxTotalPrice +"',ZCGF='"+cgfTotalPrice+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',"
								 		+ "CGF='"+CGF+"',XDXH='"+rowMap.getString("MKMC")+"',MKBH='"+rowMap.getString("MKBM")+"',XTMC='"+json.getString("XTMC")+"'"
								 	    + ",MKMC='"+json.getString("MKMC")+"',MKXH='"+json.getString("MKXH")+"' where ID='"+id+"' ; ";
									//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
									sqlList.add(upSql);
							}else {
								String sl=json.getString("SL").isEmpty()?"0":json.getString("SL");
								double number = Double.parseDouble(sl);
								number1=number;
								double MKDJ_price1 = MKDJ;
								double MKTotalPrice1 = number * MKDJ_price1;
								double clfTotalPrice1 = number * CLF;
								double rgfTotalPrice = number * RGF;
								double wxTotalPrice = number * WXWG;
								double cgfTotalPrice = number * CGF;
								String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ+"',SL='"+number+"',DW='"+rowMap.getString("DW")+"',GG='"+rowMap.getString("GG")+"',ZJ='"+MKTotalPrice1+"'"
								 		+ ",ZCLF='"+clfTotalPrice1+"',ZGSF='"+rgfTotalPrice+"',ZWX='"+wxTotalPrice +"',ZCGF='"+cgfTotalPrice+"',CLF= '"+CLF+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',"
								 		+ "CGF='"+CGF+"',XDXH='"+rowMap.getString("MKMC")+"',MKBH='"+rowMap.getString("MKBM")+"',XTMC='"+json.getString("XTMC")+"'"
								 	    + ",MKMC='"+json.getString("MKMC")+"',MKXH='"+json.getString("MKXH")+"' where ID='"+id+"' ; ";
									//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
									sqlList.add(upSql);
							}
							//更新参数信息
							if(csmoduleMap.containsKey(bm)) {
								List<RowMap> list=csmoduleMap.get(bm);
								if(list.size()>0) {
									for (RowMap rowMap2 : list) {
										String no=rowMap2.getString("PARMENTIER_NO");
										if(pMap.containsKey(no)) {
											JSONObject json1=pMap.get(no);
											String value=json1.getString("value");
											 String upSql= "update BO_EU_CPQ_MODULE_RECORD_PARAMETERS set PARMENTIER_VALUE= '"+value+"' where ID='"+rowMap2.getString("ID")+"' ; ";
												//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
												csLists.add(upSql);
										}
									}
								}
							}


							 /**
	                         * 计算原材料重量
	                         */
							for (String key:clMaps.keySet()) {

								JSONObject jsonObject=clMaps.get(key);
								System.out.println("材料信息："+jsonObject.toJSONString());
								String CLZL=jsonObject.getString("ZL");//重量
								BigDecimal zl=new BigDecimal(CLZL);
								BigDecimal sl=new BigDecimal(number1);//模块数量
								BigDecimal addzl= sl.multiply(zl);
								if(zclMaps.containsKey(key)) {
									JSONObject jsoncl=zclMaps.get(key);
									String zlold=jsoncl.getString("ZL");
									BigDecimal zlOld= new BigDecimal(zlold);
									zlOld=zlOld.add(addzl);
									jsoncl.put("ZL", zlOld);
									zclMaps.put(key, jsoncl);
								}else {
									jsonObject.put("ZL", addzl);
									zclMaps.put(key, jsonObject);
								}

							}
						}
					} catch (Exception e) {
						throw  e;
					}finally {
						try {
							workbookwl.close();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
			  			 try {
							workbook.close();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}

					}
				}
				  if(sqlList.size()>0) {
						 String[] array3 = sqlList.toArray(new String[sqlList.size()]);
						 DBSql.batch(array3);
					}
				  if(mkList.size()>0) {
						 String[] array3 = mkList.toArray(new String[mkList.size()]);
						 DBSql.batch(array3);
					}
				  if(wlList.size()>0) {
						 String[] array3 = wlList.toArray(new String[wlList.size()]);
						 DBSql.batch(array3);
					}
				  if(zfLists.size()>0) {
						 String[] array3 = zfLists.toArray(new String[zfLists.size()]);
						 DBSql.batch(array3);
					}
				  if(csLists.size()>0) {
						 String[] array3 = csLists.toArray(new String[csLists.size()]);
						 DBSql.batch(array3);
					}
				//将计算出来的原材料重量数据存放到项目申请单中
					if(zclMaps.size()>0) {
						SDK.getBOAPI().removeByBindId("BO_EU_CPQ_PROJECT_MSG_MATERIAL‌", bindId);
						List<BO> cls=SDK.getBOAPI().query("BO_EU_CPQ_PROPERTIES").list();
						Map<String, BO> clMap = new HashMap<String, BO>();
						if(cls.size()>0) {
							for (int i = 0; i < cls.size(); i++) {
								//JSONObject json = (JSONObject) list.get(i);
								BO json =  cls.get(i);
								clMap.put(json.getString("CLBM"), json);

							}
						}
						List<BO> bos2=new ArrayList<>();
						for (String key : zclMaps.keySet()) {
							BO bo=new BO();
							JSONObject json=zclMaps.get(key);
							bo.set("CLBM", json.getString("BM"));
							bo.set("CLMC", json.getString("MC"));
							bo.set("LX", json.getString("LX"));
							bo.set("GG", json.getString("XH"));
							//bo.set("DW", json.getString("DW"));
							BigDecimal zl=new BigDecimal(json.getString("ZL").isEmpty()?"0":json.getString("ZL"));
							BigDecimal rounded = zl.setScale(0, RoundingMode.HALF_UP);
							if(clMap.containsKey(key)) {
								BO bo2=clMap.get(key);
							String dj=	bo2.getString("BHSCGJ");
							bo.set("QHJ", dj);
							String rkj=	bo2.getString("RKJ");
							String lsj=	bo2.getString("LSJ");
							if(!rkj.isEmpty()) {
								BigDecimal dj1 = new BigDecimal(dj);
								BigDecimal rkj1 = new BigDecimal(rkj);
								if(rounded.compareTo(BigDecimal.ZERO)==0) {
									bo.set("RKJCE", "0");
								}else {
									BigDecimal rkcj=dj1.subtract(rkj1).multiply(rounded);
									bo.set("RKJCE", rkcj);
								}
								bo.set("RKJ", rkj);

							}else {
								bo.set("RKJ", "0");
								bo.set("RKJCE", "0");
							}
							if(!lsj.isEmpty()) {
								BigDecimal dj1 = new BigDecimal(dj);
								BigDecimal lsj1 = new BigDecimal(lsj);
								if(rounded.compareTo(BigDecimal.ZERO)==0) {
									bo.set("LSJCE", "0");
								}else {
									BigDecimal lscj=dj1.subtract(lsj1).multiply(rounded);
									bo.set("LSJCE", lscj);
								}

								bo.set("LSJ", lsj1);

							}else {
								bo.set("LSJ", "0");
								bo.set("LSJCE", "0");
							}
							}
							bo.set("CLZL", rounded);
							//System.out.println("汇总材料信息："+bo.toString());
							bos2.add(bo);
						}
						SDK.getBOAPI().create("BO_EU_CPQ_PROJECT_MSG_MATERIAL‌", bos2, bindId, userContext.getUID());
					}
					ResponseObject ro = ResponseObject.newOkResponse();
					return ro.toString();
	}

}
